package myandroid.net.DAO;

import java.sql.Date;
import java.sql.Time;
import java.util.Vector;

import myandroid.net.DTO.DTO_Task;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DAO_Task extends SQLiteOpenHelper{
	
	private SQLiteDatabase gDatabase;
	
//	private static final String gTaskID = "TaskID";
//	
//	private static final String gTaskName = "TaskName";
//	
//	private static final String gTaskDescription = "TaskDescription";
//	
//	private static final String gParentID = "ParentID";
//	
//	private static final String gTaskDate = "12/12/2012";
//	
//	private static final String gTaskStartTime = "12:12:12";
//	
//	private static final String gTaskLongTime = "5";
//	
//	private static final String gTaskAvatar = "TaskAvatar";
//	
//	private static final String gTaskStatus = "TaskStatus";
//	
//	private static final String gTaskPriority = "1";
//	
//	private static final String gTaskType = "TaskType";

	private static final String TABLE_NAME = "TASK";
	
	private static final String DATABASE_CREATE = "CREATE TABLE [" + TABLE_NAME + "] (" +
			"[TaskID] VARCHAR(10)  PRIMARY KEY NOT NULL," +
			"[TaskName] VARCHAR(100)  NOT NULL," +
			"[TaskDescription] NVARCHAR(300)  NULL," +
			"[ParentID] VARCHAR(10)  NOT NULL," +
			"[TaskDate] DATE  NOT NULL," +
			"[TaskStartTime] TIME  NULL," +
			"[TaskLongTime] INTEGER  NOT NULL," +
			"[TaskAvatar] VARCHAR(50)  NULL," +
			"[TaskStatus] VARCHAR(50)  NULL," +
			"[TaskPriority] NUMERIC  NULL," +
			"[TaskType] VARCHAR(20)  NOT NULL" +
			")";

	public DAO_Task(Context context, String DATABASE_NAME, CursorFactory factory,
			int DATABASE_VERSION) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		
		gDatabase = context.openOrCreateDatabase(DATABASE_NAME, Context.MODE_PRIVATE, factory);
		
	}

	private void openWriteDatabase() throws SQLException{
		gDatabase = getWritableDatabase();
	}
	
	private void openReadDatabase() throws SQLException{
		gDatabase = getReadableDatabase();
	}
	
	private void closeDatabase() {
		gDatabase.close();
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(DATABASE_CREATE);	
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.w(DAO_Task.class.getName(), "Upgrading database from version " + oldVersion + "to" 
				+ newVersion + ", which will destroy all old data");
		db.execSQL("DROP TABLE IF EXISTS" + TABLE_NAME);
		onCreate(db);
	}
	
	//them mot task moi vao trong database
	public void addTask(DTO_Task mTask){
		
		this.openWriteDatabase();
		
		String sql = "INSERT INTO " + TABLE_NAME + "(TaskID,TaskName,TaskDescription,ParentID" +
				"TaskDate,TaskStartTime,TaskLongTime,TaskAvatar,TaskStatus,TaskPriority,TaskType) VALUES(";
		
		sql += "'" + mTask.getgTaskID().trim() + "',";
		sql += "'" + mTask.getgTaskName().trim() + "',";;
		sql += "'" + mTask.getgTaskDescription().trim() + "',";
		sql += "'" + mTask.getgParentID().trim() + "',";
		sql += mTask.getgTaskDate().toString() + ",";
		sql += mTask.getgTaskStartTime().toString() + ",";
		sql += mTask.getgTaskLongTime() + ",";
		sql += "'" + mTask.getgTaskAvatar().trim() + "',";
		sql += "'" + mTask.getgTaskStatus().trim() + "',";
		sql += mTask.getgTaskPriority() + ",";
		sql += "'" + mTask.getgTaskType().trim() + "')";
		
		gDatabase.execSQL(sql);
		
//		ContentValues ctValues = new ContentValues();
//		
//		ctValues.put(gTaskID, mTask.getgTaskID());
//		
//		ctValues.put(gTaskName, mTask.getgTaskName());
//		
//		ctValues.put(gTaskDescription, mTask.getgTaskDescription());
//		
//		ctValues.put(gParentID, mTask.getgParentID().toString());
//		
//		ctValues.put(gTaskDate, mTask.getgTaskDate().toGMTString());
//		
//		ctValues.put(gTaskStartTime, mTask.getgTaskStartTime().toGMTString());
//		
//		ctValues.put(gTaskLongTime, String.valueOf(mTask.getgTaskLongTime()));
//		
//		ctValues.put(gTaskAvatar, mTask.getgTaskAvatar());
//		
//		ctValues.put(gTaskStatus, mTask.getgTaskStatus());
//		
//		ctValues.put(gTaskPriority, mTask.getgTaskPriority().toString());
//		
//		ctValues.put(gTaskType, mTask.getgTaskType());
//		
//		gDatabase.insert(TABLE_NAME, null, ctValues);
		
		this.closeDatabase();
		
	}
	
	//cap nhat mot task vao trong database
	public void updateTask(DTO_Task mTask){
		
		this.openWriteDatabase();	

		String sql = "UPDATE " + TABLE_NAME + " SET ";
		sql += "TaskName = '" + mTask.getgTaskName().trim() + "',";
		sql += "TaskDescription = '" + mTask.getgTaskDescription().trim() + "',";
		sql += "ParentID = '" + mTask.getgParentID().trim() + "',";
		sql += "TaskDate = " + mTask.getgTaskDate().toString() + ",";
		sql += "TaskStartTime = " + mTask.getgTaskStartTime().toString() + ",";
		sql += "TaskLongTime = " + mTask.getgTaskLongTime() + ",";
		sql += "TaskAvatar = '" + mTask.getgTaskAvatar().trim() + "',";
		sql += "TaskStatus = '" + mTask.getgTaskStatus().trim() + "',";
		sql += "TaskPriority = " + mTask.getgTaskPriority() + ",";
		sql += "TaskType = '" + mTask.getgTaskType().trim() + "' ";
		sql += "WHERE TaskID = '" + mTask.getgTaskID().trim() + "'";
		
		gDatabase.execSQL(sql);
		
//		ContentValues ctValues = new ContentValues();
//		
//		ctValues.put(gTaskName, mTask.getgTaskName());
//		
//		ctValues.put(gTaskDescription, mTask.getgTaskDescription());
//		
//		ctValues.put(gParentID, mTask.getgParentID());
//		
//		ctValues.put(gTaskAvatar, mTask.getgTaskAvatar());
//		
//		ctValues.put(gTaskDate, mTask.getgTaskDate().toGMTString());
//		
//		ctValues.put(gTaskStartTime, mTask.getgTaskStartTime().toGMTString());
//		
//		ctValues.put(gTaskLongTime, String.valueOf(mTask.getgTaskLongTime()));
//				
//		ctValues.put(gTaskAvatar, mTask.getgTaskAvatar());
//				
//		ctValues.put(gTaskStatus, mTask.getgTaskStatus());
//				
//		ctValues.put(gTaskPriority, mTask.getgTaskPriority().toString());
//				
//		ctValues.put(gTaskType, mTask.getgTaskType());
//		
//		gDatabase.update(TABLE_NAME, ctValues, gTaskID + "=?", new String[]{ String.valueOf(mTask.getgTaskID())});
		
		this.closeDatabase();
	}

	//xoa mot task da co trong database theo ma id
	public void deleteTask(String mTaskID){
		
		this.openWriteDatabase();
		
		String sql = "DELETE FROM " + TABLE_NAME + " ";
		
		sql += "WHERE TaskID = '" + mTaskID.trim() + "'";
		
		gDatabase.execSQL(sql);
		
//		gDatabase.delete(TABLE_NAME, mTaskID + "=?", new String[]{String.valueOf(mTaskID)});
		
		this.closeDatabase();
	}

	//xoa mot so task da co trong database theo ngay cua tao task
	public void deleteTask(Date mTaskDate){
		
		this.openWriteDatabase();
		
		String sql = "DELETE FROM " + TABLE_NAME + " ";
		
		sql += "WHERE TaskDate = " + mTaskDate;
		
		gDatabase.execSQL(sql);
		
//		gDatabase.delete(TABLE_NAME, mTaskID + "=?", new String[]{String.valueOf(mTaskID)});
		
		this.closeDatabase();
	}
	
	//xoa mot so task theo ma parentid
	public void deleteOfParent(String mParentID){
		
		this.openWriteDatabase();
		
		String sql = "DELETE FROM " + TABLE_NAME + " ";
		
		sql += "WHERE ParentID = '" + mParentID.trim() + "'";
		
		gDatabase.execSQL(sql);
		
//		gDatabase.delete(TABLE_NAME, mTaskID + "=?", new String[]{String.valueOf(mTaskID)});
		
		this.closeDatabase();
	}
	
	//tim kiem task theo mot so ki tu co chua trong ten cua task
	public Vector<DTO_Task> searchTaskName(String mTaskName) {
		
		this.openReadDatabase();
		
		Vector<DTO_Task> list = new Vector<DTO_Task>();
		
		DTO_Task mTask = new DTO_Task();
		
		String sql = "SELECT * FROM " + TABLE_NAME + " ";
		sql += "WHERE TaskName LIKE '%" + mTaskName.trim() + "%'";
		
		Cursor mCursor = gDatabase.rawQuery(sql, null);
		
		if(mCursor.moveToFirst())
		{
			do
			{
				mTask.setgTaskID(mCursor.getString(0).trim());
				mTask.setgTaskName(mCursor.getString(1).trim());
				mTask.setgTaskDescription(mCursor.getString(2).trim());
				mTask.setgParentID(mCursor.getString(3).trim());
				mTask.setgTaskDate(new Date(mCursor.getLong(4)));
				mTask.setgTaskStartTime(new Time(mCursor.getLong(5)));
				mTask.setgTaskLongTime(mCursor.getInt(6));
				mTask.setgTaskAvatar(mCursor.getString(7));
				mTask.setgTaskStatus(mCursor.getString(8));
				mTask.setgTaskPriority(mCursor.getInt(9));
				mTask.setgTaskType(mCursor.getString(10));
				
				list.add(mTask);
				
			}while(mCursor.moveToNext());
			
			this.closeDatabase();
			
			return list;
			
		}
		else {
			
			this.closeDatabase();
			
			return null;
		}
	}
	
	//tim kiem task theo mot so ki tu co chua trong Description cua task
	public Vector<DTO_Task> searchTaskDescrip(String mTaskDescrip) {
		
		this.openReadDatabase();
			
		Vector<DTO_Task> list = new Vector<DTO_Task>();
			
		DTO_Task mTask = new DTO_Task();
			
		String sql = "SELECT * FROM " + TABLE_NAME + " ";
		sql += "WHERE TaskDescription LIKE '%" + mTaskDescrip.trim() + "%'";
			
		Cursor mCursor = gDatabase.rawQuery(sql, null);
			
		if(mCursor.moveToFirst())
		{
			do
			{
				mTask.setgTaskID(mCursor.getString(0).trim());
				mTask.setgTaskName(mCursor.getString(1).trim());
				mTask.setgTaskDescription(mCursor.getString(2).trim());
				mTask.setgParentID(mCursor.getString(3).trim());
				mTask.setgTaskDate(new Date(mCursor.getLong(4)));
				mTask.setgTaskStartTime(new Time(mCursor.getLong(5)));
				mTask.setgTaskLongTime(mCursor.getInt(6));
				mTask.setgTaskAvatar(mCursor.getString(7));
				mTask.setgTaskStatus(mCursor.getString(8));
				mTask.setgTaskPriority(mCursor.getInt(9));
				mTask.setgTaskType(mCursor.getString(10));
					
				list.add(mTask);
					
			}while(mCursor.moveToNext());
				
			this.closeDatabase();
				
			return list;
				
		}
		else {
				
			this.closeDatabase();
				
			return null;
		}
	}
	
	//lay thong tin ve mot task theo ma id
	public DTO_Task getTask(String mTaskID) {
		
		this.openReadDatabase();
		
		DTO_Task mTask = new DTO_Task();
		
		String sql = "SELECT * FROM " + TABLE_NAME + " ";
		sql += "WHERE TaskID = '" + mTaskID.trim() + "'";
		
		Cursor mCursor = gDatabase.rawQuery(sql, null);
		
		if(mCursor.moveToFirst())
		{
			mTask.setgTaskID(mCursor.getString(0).trim());
			mTask.setgTaskName(mCursor.getString(1).trim());
			mTask.setgTaskDescription(mCursor.getString(2).trim());
			mTask.setgParentID(mCursor.getString(3).trim());
			mTask.setgTaskDate(new Date(mCursor.getLong(4)));
			mTask.setgTaskStartTime(new Time(mCursor.getLong(5)));
			mTask.setgTaskLongTime(mCursor.getInt(6));
			mTask.setgTaskAvatar(mCursor.getString(7));
			mTask.setgTaskStatus(mCursor.getString(8));
			mTask.setgTaskPriority(mCursor.getInt(9));
			mTask.setgTaskType(mCursor.getString(10));
			
			this.closeDatabase();
			return mTask;		
		}
		else {
			this.closeDatabase();
			return null;
		}	
	}
	
	//lay thong tin ve mot so task theo ngay tao task
	public Vector<DTO_Task> getTask(Date mTaskDate) {

		this.openReadDatabase();
		
		Vector<DTO_Task> list = new Vector<DTO_Task>();
		
		DTO_Task mTask = new DTO_Task();
		
		String sql = "SELECT * FROM " + TABLE_NAME + " " +
				"WHERE TaskDate = " + mTaskDate.toString();
		
		Cursor mCursor = gDatabase.rawQuery(sql, null);
		
		if(mCursor.moveToFirst())
		{
			do
			{
				mTask.setgTaskID(mCursor.getString(0).trim());
				mTask.setgTaskName(mCursor.getString(1).trim());
				mTask.setgTaskDescription(mCursor.getString(2).trim());
				mTask.setgParentID(mCursor.getString(3).trim());
				mTask.setgTaskDate(new Date(mCursor.getLong(4)));
				mTask.setgTaskStartTime(new Time(mCursor.getLong(5)));
				mTask.setgTaskLongTime(mCursor.getInt(6));
				mTask.setgTaskAvatar(mCursor.getString(7));
				mTask.setgTaskStatus(mCursor.getString(8));
				mTask.setgTaskPriority(mCursor.getInt(9));
				mTask.setgTaskType(mCursor.getString(10));
				
				list.add(mTask);
				
			}while(mCursor.moveToNext());
			
			this.closeDatabase();
			
			return list;
			
		}
		else {
			
			this.closeDatabase();
			
			return null;
		}	
	}
	
	//lay  thong tin ve mot so task theo ma parentID
	public Vector<DTO_Task> getOfParent(String mParentID){

		this.openReadDatabase();
		
		Vector<DTO_Task> list = new Vector<DTO_Task>();
		
		DTO_Task mTask = new DTO_Task();
		
		String sql = "SELECT * FROM " + TABLE_NAME + " ";
		sql += "WHERE ParentID = '" + mParentID.trim() + "'";
		
		Cursor mCursor = gDatabase.rawQuery(sql, null);
		
		if(mCursor.moveToFirst())
		{
			do
			{
				mTask.setgTaskID(mCursor.getString(0).trim());
				mTask.setgTaskName(mCursor.getString(1).trim());
				mTask.setgTaskDescription(mCursor.getString(2).trim());
				mTask.setgParentID(mCursor.getString(3).trim());
				mTask.setgTaskDate(new Date(mCursor.getLong(4)));
				mTask.setgTaskStartTime(new Time(mCursor.getLong(5)));
				mTask.setgTaskLongTime(mCursor.getInt(6));
				mTask.setgTaskAvatar(mCursor.getString(7));
				mTask.setgTaskStatus(mCursor.getString(8));
				mTask.setgTaskPriority(mCursor.getInt(9));
				mTask.setgTaskType(mCursor.getString(10));
				
				list.add(mTask);
				
			}while(mCursor.moveToNext());
			
			this.closeDatabase();
			
			return list;
			
		}
		else {
			
			this.closeDatabase();
			
			return null;
		}
	}
	
	//lay thong tin ve tat ca cac task co trong database
	public Vector<DTO_Task> getAllTask(){

		this.openReadDatabase();
		
		Vector<DTO_Task> list = new Vector<DTO_Task>();
		
		DTO_Task mTask = new DTO_Task();
		
		String sql = "SELECT * FROM " + TABLE_NAME;
		
		Cursor mCursor = gDatabase.rawQuery(sql, null);
		
		if(mCursor.moveToFirst())
		{
			do
			{
				mTask.setgTaskID(mCursor.getString(0).trim());
				mTask.setgTaskName(mCursor.getString(1).trim());
				mTask.setgTaskDescription(mCursor.getString(2).trim());
				mTask.setgParentID(mCursor.getString(3).trim());
				mTask.setgTaskDate(new Date(mCursor.getLong(4)));
				mTask.setgTaskStartTime(new Time(mCursor.getLong(5)));
				mTask.setgTaskLongTime(mCursor.getInt(6));
				mTask.setgTaskAvatar(mCursor.getString(7));
				mTask.setgTaskStatus(mCursor.getString(8));
				mTask.setgTaskPriority(mCursor.getInt(9));
				mTask.setgTaskType(mCursor.getString(10));
				
				list.add(mTask);
				
			}while(mCursor.moveToNext());
			
			this.closeDatabase();
			
			return list;
			
		}
		else {
			
			this.closeDatabase();
			
			return null;
		}
	}
	
}
